﻿using Dapper;
using OddUserManager.Data.IRepositories;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
namespace OddUserManager.Data.Repositories
{
    public class OddRepository : DapperBaseRepository<Odd, int>, IOddRepository
    {
        public new Odd Get(int id)
        {
            Odd odd = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select o.*,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id where o.Id={id};select * from BetDetailOdd where OddId={id};select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId={id})";
                odd = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).FirstOrDefault();
                if (null != odd)
                {
                    var matchSql = $"select * from OddSelectedMatchs where OddId={id}";
                    odd.OddSelectedMatchList = con.Query<OddSelectedMatchs>(matchSql).ToList();
                }
            }
            return odd;
        }

        public new IEnumerable<Odd> GetAllList()
        {
            IEnumerable<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[Certification],o.[ConfirmAppUserId],o.[OrderString],o.OrderInt,o.Descption,  ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id; select * from BetDetailOdd;select * from BetDetailOddContents;";
                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      );
            }
            return list;
        }


        public List<Odd> GetAllList(int pageIndex, int pageSize, out int dataCount)
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var countSql = $"select count(Id) from Odd";
                dataCount = con.Query<int>(countSql).FirstOrDefault();
                var sqlCount = pageIndex * pageSize;
                if (sqlCount > dataCount)
                {
                    pageSize = dataCount % pageSize;
                }

                var sql = $"SELECT o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[Certification],o.[ConfirmAppUserId],o.[OrderString],o.[Descption],o.[Title],o.OrderInt, ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName FROM odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id,(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id) w2 WHERE o.Id = w2.Id ORDER BY o.LotteryTime DESC, o.Id DESC; " +
                    $"select * from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id  ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id); " +
                    $"select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM(SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id))";
                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();

            }


            return list;
        }

        public List<Odd> GetOddsByCreateUserId(int id)
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[Certification],o.[ConfirmAppUserId],o.[OrderString],o.[Title],o.OrderInt,o.Descption, ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName,u3.Name as ConfirmAppUserName from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id left join AppUser u3 on o.ConfirmAppUserId = u3.Id where o.CreateOddUserId={id};" +
                    $"select * from BetDetailOdd where OddId in(select Id from Odd where  CreateOddUserId={id});" +
                    $"select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in(select Id from Odd where  CreateOddUserId={id}))";

                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();

            }
            return list;
        }

        public List<SelectedMatchOddData> GetSelectedMatchOddDataByMatchIds(List<int> ids)
        {
            List<SelectedMatchOddData> list = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select Sum(bb.BetCount) as BetCount,b.SelectedMatchId as MatchId,b.SelectedResult as Result FROM BetDetailOddContents b left join BetDetailOdd bb on b.BetDetailOddId=bb.Id left join Odd o on o.Id=bb.OddId left join [Site] s on s.Id = o.SiteId where s.ParentId = 80 and b.SelectedMatchId in({string.Join(",",ids)}) group by b.SelectedMatchId,b.SelectedResult";
                list = con.Query<SelectedMatchOddData>(sql).ToList();
            }
            return list;
        }



        public List<Odd> GetOddBySelectedMatchIds(List<int> ids)
        {
            List<Odd> list = new List<Odd>();
            using (var con = GetOpenConnection())
            {
                var sql = $"select ou.Account as CreateOddUserName, o.*, bb.*,b.* FROM BetDetailOddContents b left join BetDetailOdd bb on b.BetDetailOddId = bb.Id left join Odd o on o.Id = bb.OddId left join OddUser ou on o.createOddUserId=ou.Id left join [Site] s on s.Id=o.SiteId where s.ParentId = 80 and b.SelectedMatchId in({string.Join(",",ids)}) order by o.Id";
                var clist = con.Query<Odd, BetDetailOdd, BetDetailOddContents, Odd>(sql, (c, f, t) =>
                {
                   
                    var odd = list.Find(x => x.Id == c.Id);
                    if (odd == null)
                    {
                        f.BetDetailOddContentsList.Add(t);
                        if (c.BetDetailOddList == null) { c.BetDetailOddList = new List<BetDetailOdd>(); }
                        c.BetDetailOddList.Add(f);
                        list.Add(c);
                        return c;
                    }
                    else
                    {
                        var a = odd.BetDetailOddList.FirstOrDefault(p => p.Id == f.Id);
                        if (a == null)
                        {
                            f.BetDetailOddContentsList.Add(t);
                            odd.BetDetailOddList.Add(f);
                        }
                        else {
                            var b = a.BetDetailOddContentsList.FirstOrDefault(p => p.Id == t.Id);
                            if (null == b)
                            {
                                a.BetDetailOddContentsList.Add(t);
                            }

                        }
                        return odd;
                    }
                }, splitOn: "Id").ToList();

            }
            return list;
        }


        public List<Odd> GetOddsBySiteIds(string siteIds, int pageIndex, int pageSize, out int dataCount)
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var countSql = $"select count(o.Id) from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id where ou.SiteId in ({siteIds})";
                dataCount = con.Query<int>(countSql).FirstOrDefault();
                var sqlCount = pageIndex * pageSize;
                if (sqlCount > dataCount)
                {
                    pageSize = dataCount % pageSize;
                }


                var sql = $"SELECT o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[Certification],o.[ConfirmAppUserId],o.[OrderString],o.OrderInt,o.Descption, ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName FROM odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id,(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id where ku.SiteId in({siteIds})ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id) w2 WHERE o.Id = w2.Id ORDER BY o.LotteryTime DESC, o.Id DESC; " +
                    $"select * from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id where ku.SiteId in({siteIds}) ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id); " +
                    $"select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM(SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id where ku.SiteId in({siteIds}) ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id))";

                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();

            }


            return list;
        }

        public bool UpdateOddDetailsContents(int id, List<BetDetailOdd> list)
        {
            bool isSuccess = false;
            var t = Get(id);
            using (var con = GetOpenConnection())
            {
                var trans = con.BeginTransaction();
                try
                {
                    if (null != t)
                    {
                        var deleteSql = $"delete from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId={id});delete from BetDetailOdd where OddId={id};";
                        con.Execute(deleteSql, null, trans, 3000, null);
                        if (null != list && list.Count > 0)
                        {
                            list.ForEach(p =>
                            {
                                var childSql = $"insert into BetDetailOdd (OddId,BetCount,Remark,Bonus) values({id},{p.BetCount},'{p.Remark}',{p.Bonus});SELECT SCOPE_IDENTITY()";
                                var BetDetailOddId = con.ExecuteScalar<int>(childSql, null, trans, 3000, null);
                                if (null != p.BetDetailOddContentsList && p.BetDetailOddContentsList.Count > 0)
                                {
                                    p.BetDetailOddContentsList.ForEach(x =>
                                    {
                                        var child2Sql = $"insert into BetDetailOddContents (BetDetailOddId ,SelectedMatchId,SelectedResult,SelectedOdds,FixOdds) values({BetDetailOddId},{x.SelectedMatchId},{(int)x.SelectedResult},{x.SelectedOdds},'{x.FixOdds}')";
                                        con.Execute(child2Sql, null, trans, 3000, null);
                                    });
                                }
                            });
                        }

                    }
                    trans.Commit();
                    isSuccess = true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return isSuccess;
        }


        public bool InsertOrUpdate(Odd entity)
        {
            bool isSuccess = false;
            var t = Get(entity.Id);
            using (var con = GetOpenConnection())
            {
                var trans = con.BeginTransaction();
                try
                {
                    if (null == t)
                    {
                        var propertyContainer = ParseProperties(entity);
                        var sql = $"insert into {typeof(Odd).Name} ({string.Join(", ", propertyContainer.ValueNames)}) values (@{string.Join(", @", propertyContainer.ValueNames)});SELECT SCOPE_IDENTITY()";

                        //var sql = $"insert into Odd ([CreateTime],[BespeakCutoffTime],[DocumentaryCutoffTime],[SiteId],[CreateOddUserId],[OwnPurchaseMoney],[DocumentaryUserCount],[DocumentaryMoney],[OwnPurchaseRebate],[DocumentaryRebate],[OwnBonus],[DocumentaryBonus],[EstimatedCommission], [ActualCommission],[AppUserId],[LotteryTime], [State],[BetWay],[Remark]) values('{entity.CreateTime}','{entity.BespeakCutoffTime}','{entity.DocumentaryCutoffTime}',{entity.SiteId},{entity.CreateOddUserId},{entity.OwnPurchaseMoney},{entity.DocumentaryUserCount},{entity.DocumentaryMoney},{entity.OwnPurchaseRebate},{entity.DocumentaryRebate},{entity.OwnBonus},{entity.DocumentaryBonus},{entity.EstimatedCommission},{entity.ActualCommission},{entity.AppUserId},'{entity.LotteryTime}',{(int)entity.State},'{entity.BetWay}','{entity.Remark}'); SELECT SCOPE_IDENTITY()";
                        var id = con.ExecuteScalar<int>(sql, propertyContainer.ValuePairs, trans, 3000, null);
                        if (null != entity.BetDetailOddList && entity.BetDetailOddList.Count > 0)
                        {
                            entity.BetDetailOddList.ForEach(p =>
                            {
                                var childSql = $"insert into BetDetailOdd (OddId,BetCount,Remark,Bonus,LotteryResults) values({id},{p.BetCount},'{p.Remark}',{p.Bonus},{(int)p.LotteryResults});SELECT SCOPE_IDENTITY()";
                                var BetDetailOddId = con.ExecuteScalar<int>(childSql, null, trans, 3000, null);
                                if (null != p.BetDetailOddContentsList && p.BetDetailOddContentsList.Count > 0)
                                {
                                    p.BetDetailOddContentsList.ForEach(x =>
                                    {
                                        var child2Sql = $"insert into BetDetailOddContents (BetDetailOddId ,SelectedMatchId,SelectedResult,SelectedOdds,FixOdds,WeekName,LotteryResults) values({BetDetailOddId},{x.SelectedMatchId},{(int)x.SelectedResult},{x.SelectedOdds},'{x.FixOdds}','{x.WeekName}',{(int)x.LotteryResults})";
                                        con.Execute(child2Sql, null, trans, 3000, null);
                                    });
                                }
                            });
                        }

                        if (null != entity.OddSelectedMatchList && entity.OddSelectedMatchList.Count > 0)
                        {
                            entity.OddSelectedMatchList.ForEach(p =>
                            {
                                var childSql = $"insert into OddSelectedMatchs(OddId,SelectedMatchId,SelectedJCHadOddId,SelectedJCHHadOddId,SelectedJCHAFUOddId,SelectedJCHadOdds,SelectedJCHHadOdds,SelectedJCHAFUOdds,Dan) values({id},{p.SelectedMatchId},{p.SelectedJCHadOddId},{p.SelectedJCHHadOddId},{p.SelectedJCHAFUOddId},'{p.SelectedJCHadOdds}','{p.SelectedJCHHadOdds}','{p.SelectedJCHAFUOdds}',{(p.Dan ? 1 : 0)})";
                                con.ExecuteScalar<int>(childSql, null, trans, 3000, null);
                            });
                        }

                    }
                    else
                    {
                        var sql = "update Odd set CreateTime=@CreateTime,BespeakCutoffTime=@BespeakCutoffTime,DocumentaryCutoffTime=@DocumentaryCutoffTime,SiteId=@SiteId,CreateOddUserId=@CreateOddUserId,OwnPurchaseMoney=@OwnPurchaseMoney,DocumentaryUserCount=@DocumentaryUserCount,DocumentaryMoney=@DocumentaryMoney,OwnBonus=@OwnBonus,DocumentaryBonus=@DocumentaryBonus,EstimatedCommission=@EstimatedCommission,ActualCommission=@ActualCommission,LastEditAppUserId=@LastEditAppUserId,LotteryTime=@LotteryTime,State=@State,BetWay=@BetWay,Remark=@Remark,AppUserId=@AppUserId, Result=@Result,ProfitAndLoss=@ProfitAndLoss,Divisor=@Divisor,OrderString=@OrderString,OrderInt=@OrderInt,Descption=@Descption,Title=@Title where Id=@Id";
                        con.Execute(sql, entity, trans, 3000, null);
                        var deleteSql = $"delete from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId={entity.Id});delete from BetDetailOdd where OddId={entity.Id};";
                        con.Execute(deleteSql, null, trans, 3000, null);
                        if (null != entity.BetDetailOddList && entity.BetDetailOddList.Count > 0)
                        {
                            entity.BetDetailOddList.ForEach(p =>
                            {
                                var childSql = $"insert into BetDetailOdd (OddId,BetCount,Remark,Bonus,LotteryResults) values({entity.Id},{p.BetCount},'{p.Remark}',{p.Bonus},{(int)p.LotteryResults});SELECT SCOPE_IDENTITY()";
                                var BetDetailOddId = con.ExecuteScalar<int>(childSql, null, trans, 3000, null);
                                if (null != p.BetDetailOddContentsList && p.BetDetailOddContentsList.Count > 0)
                                {
                                    p.BetDetailOddContentsList.ForEach(x =>
                                    {
                                        var child2Sql = $"insert into BetDetailOddContents (BetDetailOddId ,SelectedMatchId,SelectedResult,SelectedOdds,FixOdds,WeekName,LotteryResults) values({BetDetailOddId},{x.SelectedMatchId},{(int)x.SelectedResult},{x.SelectedOdds},'{x.FixOdds}','{x.WeekName}',{(int)x.LotteryResults})";
                                        con.Execute(child2Sql, null, trans, 3000, null);
                                    });
                                }
                            });
                        }
                        var deleteSql1 = $"delete from OddSelectedMatchs where OddId={entity.Id}";
                        con.Execute(deleteSql1, null, trans, 3000, null);
                        if (null != entity.OddSelectedMatchList && entity.OddSelectedMatchList.Count > 0)
                        {
                            entity.OddSelectedMatchList.ForEach(p =>
                            {
                                var childSql = $"insert into OddSelectedMatchs(OddId,SelectedMatchId,SelectedJCHadOddId,SelectedJCHHadOddId,SelectedJCHAFUOddId,SelectedJCHadOdds,SelectedJCHHadOdds,SelectedJCHAFUOdds,Dan) values({entity.Id},{p.SelectedMatchId},{p.SelectedJCHadOddId},{p.SelectedJCHHadOddId},{p.SelectedJCHAFUOddId},'{p.SelectedJCHadOdds}','{p.SelectedJCHHadOdds}','{p.SelectedJCHAFUOdds}',{(p.Dan ? 1 : 0)})";
                                con.Execute(childSql, null, trans, 3000, null);
                            });
                        }


                    }
                    trans.Commit();
                    isSuccess = true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return isSuccess;
        }



        public IEnumerable<Odd> LoadPageList(int startPage, int pageSize, out int rowCount, Func<Odd, bool> where, Func<Odd, object> order)
        {
            var result = GetAllList();
            if (where != null)
                result = result.Where(where).ToList();
            if (order != null)
                result = result.OrderByDescending(order);
            else
                result = result.OrderBy(m => m.Id);
            rowCount = result.Count();
            return result.Skip((startPage - 1) * pageSize).Take(pageSize);
        }

        public List<Odd> GetUnfinishedOdds()
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                //代开奖，比赛中的投注单都取出来
                var sql = "select o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[OrderString],o.OrderInt,o.Descption,o.Title, ou.SiteId as SiteId , ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id where o.[State] in(3,4,5);select * from BetDetailOdd where OddId in(select Id from Odd where [State] in(3,4,5));select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in (select Id from Odd where [State] in(3,4,5)))";
                list = con.QueryMultiple(sql, null, null,3000,null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();
                if (null != list && list.Count > 0)
                {
                    list.ForEach(p =>
                    {
                        var matchSql = $"select * from OddSelectedMatchs where OddId={p.Id}";
                        p.OddSelectedMatchList = con.Query<OddSelectedMatchs>(matchSql).ToList();

                    });
                }
            }
            return list;
        }



        public void DeleteById(int id)
        {
            using (var con = GetOpenConnection())
            {
                var sql = $"delete from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId={id});" +
                    $"delete from Odd where Id={id};" +
                    $"delete from BetDetailOdd where OddId={id};" +
                    $"delete from OddSelectedMatchs where OddId={id};";
                con.Execute(sql);
            }
        }
        public void UpdateCertification(int oddId, bool certification, int confirmAppUserId) {
            using (var con = GetOpenConnection())
            {
                var baseSql = $"select * from Odd where Id={oddId}";
                var odd = con.Query<Odd>(baseSql).FirstOrDefault();
                if (null != odd && odd.State == OddState.待上传)
                {
                    var sql = $"update Odd set Certification={(certification ? 1 : 0)},ConfirmAppUserId={confirmAppUserId},State={((int)OddState.已上传)} where Id={oddId}";
                    con.Execute(sql, null, null, 3000, null);
                }
                else {
                    var sql = $"update Odd set Certification={(certification ? 1 : 0)},ConfirmAppUserId={confirmAppUserId} where Id={oddId}";
                    con.Execute(sql, null, null, 3000, null);
                }
            }
        }

        public List<Odd> GetOddsBySiteIds(string siteIds, int[] oddMoneyTypes, int oddState, int pageIndex, int pageSize, out int dataCount)
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var countSql = $"select count(o.Id) from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id where ou.SiteId in ({siteIds})";
                dataCount = con.Query<int>(countSql).FirstOrDefault();
                var sqlCount = pageIndex * pageSize;
                if (sqlCount > dataCount)
                {
                    pageSize = dataCount % pageSize;
                }

                var con_sql = " 1=0 ";
                if (oddMoneyTypes != null && oddMoneyTypes.Length > 0)
                {
                    for (var i = 0; i < oddMoneyTypes.Length; i++)
                    {
                        if (oddMoneyTypes[i] == 1)
                        {
                            con_sql += " or k.OwnPurchaseMoney < 1000 ";
                        }
                        else if (oddMoneyTypes[i] == 2)
                        {
                            con_sql += " or (k.OwnPurchaseMoney >= 1000 and k.OwnPurchaseMoney < 10000) ";
                        }
                        else if (oddMoneyTypes[i] == 3)
                        {
                            con_sql += " or k.OwnPurchaseMoney >= 10000 ";
                        }
                    }
                }
                con_sql = " (" + con_sql + ") ";
                if (oddState > 0)
                {
                    con_sql += $" and k.state = {oddState}";
                }


                var sql = $"SELECT o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[OrderString],o.[Certification],o.[ConfirmAppUserId],o.[Descption], ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName,u3.Name as ConfirmAppUserName FROM odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id left join AppUser u3 on o.ConfirmAppUserId = u3.Id,(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id and {con_sql} where ku.SiteId in({siteIds})  ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id) w2 WHERE o.Id = w2.Id  ORDER BY o.LotteryTime DESC, o.Id DESC; " +
                    $"select * from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id and {con_sql} where ku.SiteId in({siteIds}) ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id); " +
                    $"select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM(SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id and {con_sql} where ku.SiteId in({siteIds}) ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id))";
                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();

            }


            return list;
        }

        public List<Odd> GetAllList(int[] oddMoneyTypes, int oddState, int pageIndex, int pageSize, out int dataCount)
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var countSql = $"select count(Id) from Odd";
                dataCount = con.Query<int>(countSql).FirstOrDefault();
                var sqlCount = pageIndex * pageSize;
                if (sqlCount > dataCount)
                {
                    pageSize = dataCount % pageSize;
                }

                var con_sql = " 1=0 ";
                if (oddMoneyTypes != null && oddMoneyTypes.Length > 0)
                {
                    for (var i = 0; i < oddMoneyTypes.Length; i++)
                    {
                        if (oddMoneyTypes[i] == 1)
                        {
                            con_sql += " or k.OwnPurchaseMoney < 1000 ";
                        }
                        else if (oddMoneyTypes[i] == 2)
                        {
                            con_sql += " or (k.OwnPurchaseMoney >= 1000 and k.OwnPurchaseMoney < 10000) ";
                        }
                        else if (oddMoneyTypes[i] == 3)
                        {
                            con_sql += " or k.OwnPurchaseMoney >= 10000 ";
                        }
                    }
                }
                con_sql = " (" + con_sql + ") ";

                if (oddState > 0)
                {
                    con_sql += $" and k.state = {oddState}";
                }

                var sql = $"SELECT o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[OrderString],o.[Certification],o.[ConfirmAppUserId],o.[Descption], ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName,u3.Name as ConfirmAppUserName FROM odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id left join AppUser u3 on o.ConfirmAppUserId = u3.Id,(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id and {con_sql} ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id) w2 WHERE o.Id = w2.Id ORDER BY o.LotteryTime DESC, o.Id DESC; " +
                    $"select * from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM (SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id and {con_sql}  ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id); " +
                    $"select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in(SELECT TOP {pageSize} Id FROM(SELECT TOP {sqlCount} k.Id, k.LotteryTime FROM odd k inner join OddUser ku on k.CreateOddUserId = ku.Id and {con_sql} ORDER BY k.LotteryTime DESC, k.Id DESC) w ORDER BY w.LotteryTime,w.Id))";
                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();

            }


            return list;
        }


        public List<Odd> GetOddBySiteIdsAndTime(DateTime startTime, DateTime endTime, string siteIds)
        {
            List<Odd> list = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select o.[Id] ,o.[CreateTime] ,o.[BespeakCutoffTime] ,o.[DocumentaryCutoffTime] ,o.[CreateOddUserId],o.[OwnPurchaseMoney] ,o.[DocumentaryUserCount],o.[DocumentaryMoney],o.[OwnPurchaseRebate],o.[DocumentaryRebate] ,o.[OwnBonus] ,o.[DocumentaryBonus] ,o.[EstimatedCommission],o.[ActualCommission] ,o.[AppUserId] ,o.[LotteryTime],o.[State],o.[BetWay],o.[Remark] ,o.[LastEditAppUserId] ,o.[Result],o.[ProfitAndLoss] ,o.[Divisor],o.[Certification],o.[ConfirmAppUserId],o.[OrderString],o.OrderInt,o.Descption,o.Title, ou.SiteId as SiteId ,ou.Account as CreateOddUserName,u1.Name as AppUserName,u2.Name as LastEditAppUserName,u3.Name as ConfirmAppUserName from Odd o inner join OddUser ou on o.CreateOddUserId = ou.Id inner join AppUser u1 on o.AppUserId = u1.Id inner join AppUser u2 on o.LastEditAppUserId = u2.Id left join AppUser u3 on o.ConfirmAppUserId = u3.Id " +
                    $"where LotteryTime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and LotteryTime<='{endTime.ToString("yyyy-MM-dd HH:mm:ss")}' and o.SiteId in({siteIds});"+
                    $"select * from BetDetailOdd where OddId in(select Id from Odd where LotteryTime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and LotteryTime<='{endTime.ToString("yyyy-MM-dd HH:mm:ss")}' and SiteId in({siteIds})); " +
                    $"select * from BetDetailOddContents where BetDetailOddId in(select Id from BetDetailOdd where OddId in(select Id from Odd where LotteryTime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and LotteryTime<='{endTime.ToString("yyyy-MM-dd HH:mm:ss")}' and SiteId in({siteIds})))";
                list = con.QueryMultiple(sql, null, null).Map<Odd, BetDetailOdd, BetDetailOddContents, int, int>
                      (
                      a => a.Id,
                      b => b.OddId,
                      c => c.Id,
                      d => d.BetDetailOddId,
                      (m, n) => { m.BetDetailOddList = n.ToList(); },
                      (x, y) => { x.BetDetailOddContentsList = y.OrderBy(p => p.WeekName).ToList(); }
                      ).ToList();
            }

            return list;
        }
    }
}
